The Coral Recruit dataset contains juvenile coral observations collected at UVS stations using standardized quadrat surveys. Each 50-meter transect includes ten 0.5 × 0.5 m quadrats, placed at fixed 5-meter intervals along the transect line, for a total surveyed area of 2.5 m² per station.
Data are stored in two tables:
uvs.recruits_stations — One row per station with metadata and summary metrics
uvs.recruits_observations — One row per recruit observation, including genus, size, and quadrat
All records are linked by ps_station_id. Taxonomic fields include accepted_name and accepted_aphia_id, standardized to the genus level.
Tables
Stations (uvs.recruits_stations)
This table stores metadata and summary metrics for each coral recruit survey station. Each row corresponds to a depth-stratified UVS site where coral recruits were surveyed using a series of ten 0.25 m² quadrats (total area = 2.5 m²). Fields include spatial context, survey effort, and total recruit counts and density.
Table 1: Schema for uvs.recruits_stations: metadata and summary metrics for coral recruit stations
Field
Type
Required
Description
ps_station_id
STRING
true
Unique station ID (ps_site_id_depth).
ps_site_id
STRING
true
Foreign key to uvs.sites.
exp_id
STRING
true
Expedition ID (ISO3_YEAR).
method
STRING
true
Survey method (uvs).
protocol
STRING
true
Survey protocol (coral_recruits).
divers
STRING
true
Divers who conducted the recruit survey.
region
STRING
true
Region name.
subregion
STRING
true
Subregion name.
locality
STRING
false
Optional local feature (e.g., reef, bay).
latitude
FLOAT
true
Latitude of station (decimal degrees).
longitude
FLOAT
true
Longitude of station (decimal degrees).
date
DATE
true
Date of coral recruit survey.
time
TIME
true
Start time of the survey.
depth_m
FLOAT
true
Average station depth (m).
depth_strata
STRING
true
Depth bin: supershallow, shallow, deep.
habitat
STRING
true
Habitat type.
exposure
STRING
true
Exposure type.
n_quadrats
INTEGER
true
Number of quadrats surveyed (typically 10).
quadrat_area_m2
FLOAT
true
Area of each quadrat in m² (typically 0.25).
survey_area_m2
FLOAT
true
Total area surveyed (e.g., 2.5 m²).
total_count
INTEGER
false
Total number of coral recruits recorded.
count_m2
FLOAT
false
Recruit density (individuals/m²).
notes
STRING
false
Optional QA or field notes.
Observations (uvs.recruits_observations)
This table contains one row per juvenile coral colony observed within a quadrat. Records include genus-level identification, maximum diameter (cm), and quadrat number. All observations are linked to a survey station via ps_station_id.
Table 2: Schema for uvs.recruits_observations: coral recruit observations by quadrat
Field
Type
Required
Description
obs_id
STRING
true
Unique observation ID (e.g., FJI_2025_rec_JSD_0001).
Recorded depth (m) at which the transect was conducted.
quadrat
INTEGER
true
Quadrat number (1–10) at fixed intervals along the transect.
accepted_name
STRING
true
Genus-level scientific name (Genus sp.) of the coral recruit.
accepted_aphia_id
INTEGER
true
WoRMS AphiaID — foreign key to taxonomy.corals.
diameter_cm
FLOAT
true
Maximum colony diameter (cm), measured in the field.
notes
STRING
false
Optional comments or QA annotations.
Density (uvs.recruits_density)
This table contains station-level summaries of coral recruit density by taxon, derived from raw counts in replicate quadrats along UVS transects. Each row represents a unique combination of ps_station_id and coral taxon, with recruit density standardized to individuals per square meter.
Coral recruits are defined as juvenile colonies <5 cm in maximum diameter, recorded within 10 haphazardly placed 0.25 m² quadrats (total survey area = 2.5 m² per transect). Recruits are typically identified to genus, and the standardized accepted_name and accepted_aphia_id fields represent the minimum taxonomic resolution confidently assigned to each observation.
Taxonomic information is joined from taxonomy.corals, and all entries are linked to the parent survey station via ps_station_id.
Table 3: Schema for uvs.recruits_density_by_taxa: coral recruit density summaries by station and taxon
Field
Type
Required
Description
ps_station_id
STRING
true
Unique station ID (ps_site_id_depth).
accepted_name
STRING
true
Standardized taxon name (typically genus).
accepted_aphia_id
INTEGER
true
WoRMS AphiaID — foreign key to taxonomy.corals.
family
STRING
true
Family of the coral taxon.
growth_form
STRING
false
Typical growth form of the genus (e.g., branching, massive, encrusting).
region
STRING
true
Region name.
subregion
STRING
true
Subregion name.
locality
STRING
false
Optional local feature (e.g., reef, bay, cove).
depth_m
FLOAT
true
Mean depth (m) of the station.
depth_strata
STRING
true
Depth bin: supershallow, shallow, or deep.
habitat
STRING
true
Dominant habitat type at the station.
exposure
STRING
true
Wave/wind exposure at the station.
total_count
INTEGER
false
Total number of recruits observed across all quadrats.
density_m2
FLOAT
false
Recruit density (individuals/m²), standardized by total quadrat area (2.5 m²).
---title: "Coral Recruits Surveys"format: html: theme: lux self-contained: true code-fold: true toc: true toc-depth: 3 toc-location: right number-sections: false---```{r setup, message = F, warning = F, fig.width = 10, fig.height = 10, echo = F}options(scipen = 999)library(PristineSeasR)library(bigrquery)library(gt)library(gtExtras)library(tibble)library(tidyverse)library(dplyr)library(ggplot2)library(plotly)library(lubridate)knitr::opts_chunk$set(eval = F, warning = F, message = F, include = F, echo = F)ps_paths <- PristineSeasR::get_sci_drive_paths()prj_path <- file.path(ps_paths$projects, "legacy-db")ps_data_path <- ps_paths$datasetsbigrquery::bq_auth(email = "marine.data.science@ngs.org")project_id <- "pristine-seas"bq_connection <- DBI::dbConnect(bigrquery::bigquery(), project = project_id)```#### OverviewThe Coral Recruit dataset contains juvenile coral observations collected at UVS stations using standardized quadrat surveys. Each 50-meter transect includes ten 0.5 × 0.5 m quadrats, placed at fixed 5-meter intervals along the transect line, for a total surveyed area of 2.5 m² per station.Data are stored in two tables:- `uvs.recruits_stations` — One row per station with metadata and summary metrics- `uvs.recruits_observations` — One row per recruit observation, including genus, size, and quadratAll records are linked by ps_station_id. Taxonomic fields include accepted_name and accepted_aphia_id, standardized to the genus level.------------------------------------------------------------------------#### Tables ------------------------------------------------------------------------##### Stations (`uvs.recruits_stations`)This table stores metadata and summary metrics for each coral recruit survey station. Each row corresponds to a depth-stratified UVS site where coral recruits were surveyed using a series of ten 0.25 m² quadrats (total area = 2.5 m²). Fields include spatial context, survey effort, and total recruit counts and density.```{r eval = T, include = T}#| label: tbl-recruits-stations-schema#| tbl-cap: "Schema for `uvs.recruits_stations`: metadata and summary metrics for coral recruit stations"recruits_stations_fields <- tribble( ~field, ~type, ~required, ~description, # Identifiers "ps_station_id", "STRING", TRUE, "Unique station ID (`ps_site_id_depth`).", "ps_site_id", "STRING", TRUE, "Foreign key to `uvs.sites`.", "exp_id", "STRING", TRUE, "Expedition ID (`ISO3_YEAR`).", "method", "STRING", TRUE, "Survey method (`uvs`).", "protocol", "STRING", TRUE, "Survey protocol (`coral_recruits`).", "divers", "STRING", TRUE, "Divers who conducted the recruit survey.", # Spatial "region", "STRING", TRUE, "Region name.", "subregion", "STRING", TRUE, "Subregion name.", "locality", "STRING", FALSE, "Optional local feature (e.g., reef, bay).", "latitude", "FLOAT", TRUE, "Latitude of station (decimal degrees).", "longitude", "FLOAT", TRUE, "Longitude of station (decimal degrees).", "date", "DATE", TRUE, "Date of coral recruit survey.", "time", "TIME", TRUE, "Start time of the survey.", # Environment "depth_m", "FLOAT", TRUE, "Average station depth (m).", "depth_strata", "STRING", TRUE, "Depth bin: `supershallow`, `shallow`, `deep`.", "habitat", "STRING", TRUE, "Habitat type.", "exposure", "STRING", TRUE, "Exposure type.", # Effort and summaries "n_quadrats", "INTEGER", TRUE, "Number of quadrats surveyed (typically 10).", "quadrat_area_m2", "FLOAT", TRUE, "Area of each quadrat in m² (typically 0.25).", "survey_area_m2", "FLOAT", TRUE, "Total area surveyed (e.g., 2.5 m²).", "total_count", "INTEGER", FALSE, "Total number of coral recruits recorded.", "count_m2", "FLOAT", FALSE, "Recruit density (individuals/m²).", # Notes "notes", "STRING", FALSE, "Optional QA or field notes.")gt(recruits_stations_fields) |> cols_label(field = md("**Field**"), type = md("**Type**"), required = md("**Required**"), description = md("**Description**")) |> cols_width(field ~ px(200), type ~ px(100), required ~ px(80), description ~ px(500)) |> data_color(columns = c(field), fn = scales::col_factor(palette = c("#f6f6f6"), domain = NULL) ) |> tab_options(table.font.size = px(13), table.width = pct(100)) |> fmt_tf(columns = required, tf_style = "true-false") |> fmt_markdown(columns = description) |> gt_theme_nytimes()```---##### Observations (`uvs.recruits_observations`)This table contains one row per juvenile coral colony observed within a quadrat. Records include genus-level identification, maximum diameter (cm), and quadrat number. All observations are linked to a survey station via ps_station_id.```{r eval = T, include = T}#| label: tbl-recruits-observations-schema#| tbl-cap: "Schema for `uvs.recruits_observations`: coral recruit observations by quadrat"recruits_observations_fields <- tribble( ~field, ~type, ~required, ~description, # Identification and traceability "obs_id", "STRING", TRUE, "Unique observation ID (e.g., `FJI_2025_rec_JSD_0001`).", "ps_station_id", "STRING", TRUE, "Foreign key to `uvs.recruits_stations`.", "diver", "STRING", TRUE, "Name of the diver who recorded the observation.", # Spatial context "station_label", "STRING", TRUE, "Field-assigned stratum label (e.g., `shallow`, `deep`).", "depth_m", "FLOAT", TRUE, "Recorded depth (m) at which the transect was conducted.", "quadrat", "INTEGER", TRUE, "Quadrat number (1–10) at fixed intervals along the transect.", # Taxonomic identity "accepted_name", "STRING", TRUE, "Genus-level scientific name (`Genus sp.`) of the coral recruit.", "accepted_aphia_id", "INTEGER", TRUE, "WoRMS AphiaID — foreign key to `taxonomy.corals`.", # Morphometrics "diameter_cm", "FLOAT", TRUE, "Maximum colony diameter (cm), measured in the field.", # Notes "notes", "STRING", FALSE, "Optional comments or QA annotations.")gt(recruits_observations_fields) |> cols_label(field = md("**Field**"), type = md("**Type**"), required = md("**Required**"), description = md("**Description**")) |> cols_width(field ~ px(200), type ~ px(100), required ~ px(80), description ~ px(500)) |> data_color(columns = c(field), fn = scales::col_factor(palette = c("#f6f6f6"), domain = NULL) ) |> tab_options(table.font.size = px(13), table.width = pct(100)) |> fmt_tf(columns = required, tf_style = "true-false") |> fmt_markdown(columns = description) |> gt_theme_nytimes()```---##### Density (`uvs.recruits_density`)This table contains station-level summaries of coral recruit density by taxon, derived from raw counts in replicate quadrats along UVS transects. Each row represents a unique combination of ps_station_id and coral taxon, with recruit density standardized to individuals per square meter.Coral recruits are defined as juvenile colonies <5 cm in maximum diameter, recorded within 10 haphazardly placed 0.25 m² quadrats (total survey area = 2.5 m² per transect). Recruits are typically identified to genus, and the standardized accepted_name and accepted_aphia_id fields represent the minimum taxonomic resolution confidently assigned to each observation.Taxonomic information is joined from taxonomy.corals, and all entries are linked to the parent survey station via ps_station_id.```{r eval = T, include = T}#| label: tbl-recruits-density-schema#| tbl-cap: "Schema for `uvs.recruits_density_by_taxa`: coral recruit density summaries by station and taxon"recruits_density_by_taxa_fields <- tribble( ~field, ~type, ~required, ~description, # Identifiers "ps_station_id", "STRING", TRUE, "Unique station ID (`ps_site_id_depth`).", "accepted_name", "STRING", TRUE, "Standardized taxon name (typically genus).", "accepted_aphia_id", "INTEGER", TRUE, "WoRMS AphiaID — foreign key to `taxonomy.corals`.", # Taxonomic traits "family", "STRING", TRUE, "Family of the coral taxon.", "growth_form", "STRING", FALSE, "Typical growth form of the genus (e.g., branching, massive, encrusting).", # Station spatial context "region", "STRING", TRUE, "Region name.", "subregion", "STRING", TRUE, "Subregion name.", "locality", "STRING", FALSE, "Optional local feature (e.g., reef, bay, cove).", "depth_m", "FLOAT", TRUE, "Mean depth (m) of the station.", "depth_strata", "STRING", TRUE, "Depth bin: `supershallow`, `shallow`, or `deep`.", "habitat", "STRING", TRUE, "Dominant habitat type at the station.", "exposure", "STRING", TRUE, "Wave/wind exposure at the station.", # Aggregated metrics "total_count", "INTEGER", FALSE, "Total number of recruits observed across all quadrats.", "density_m2", "FLOAT", FALSE, "Recruit density (individuals/m²), standardized by total quadrat area (2.5 m²).")gt(recruits_density_by_taxa_fields) |> cols_label(field = md("**Field**"), type = md("**Type**"), required = md("**Required**"), description = md("**Description**")) |> cols_width(field ~ px(200), type ~ px(100), required ~ px(80), description ~ px(500)) |> data_color(columns = c(field), fn = scales::col_factor(palette = c("#f6f6f6"), domain = NULL) ) |> tab_options(table.font.size = px(13), table.width = pct(100)) |> fmt_tf(columns = required, tf_style = "true-false") |> fmt_markdown(columns = description) |> gt_theme_nytimes()```